/*
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */
package main;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;

import org.apache.log4j.Logger;

import tools.BCrypt;
import tools.StringTools;

import commandhandlers.UserHandler;

import database.DBConnectionFactory;

/**
 * Provides static access to convenient methods that are useful in different situations for different handles/classes
 * 
 * 
 */
public final class CommonMethods {
   /**
    * Registers an action (can be adding a SoT, or committing some op)
    * 
    * @param sender
    * @param actiontype
    */
   public static void addAction(final String sender, final String actiontype) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM " + "actions WHERE actiontype = ? AND performer = ?");
		 ps.setString(1, actiontype);
		 ps.setString(2, sender);
		 rs = ps.executeQuery();

		 int amount = 1;
		 if (rs.next()) {
			amount = rs.getInt("amount") + 1;
		 }

		 try {
			ps.close();
		 } catch (final Exception e) {
		 }

		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("DELETE FROM " + "actions WHERE actiontype = ? AND performer = ?");
		 ps.setString(1, actiontype);
		 ps.setString(2, sender);
		 ps.executeUpdate();

		 try {
			ps.close();
		 } catch (final Exception e) {
		 }

		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("INSERT INTO actions VALUES(?,?,?)");
		 ps.setString(1, actiontype);
		 ps.setString(2, sender);
		 ps.setInt(3, amount);
		 ps.executeUpdate();
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
   }

   /**
    * Adds some default information for the specified kd
    * 
    * @param kd
    * @return
    */
   public static boolean addDefaultKD(final String kd) {
	  boolean out = false;
	  PreparedStatement ps = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("INSERT INTO kd VALUES(?,'','','','',?,false,false)");
		 ps.setString(1, kd.trim());
		 ps.setTimestamp(2, new Timestamp(GregorianCalendar.getInstance().getTimeInMillis()));
		 if (ps.executeUpdate() == 1) {
			out = true;
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Sends sms notifications to people registered for the specified event type
    * 
    * @param nick
    * @param type
    * @param msg
    */
   public static void doSMSNotification(final String nick, final int type, final String msg) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 if (type == 1 || type == 3) { // for waves or orders you want to notify several people
			ps = DBConnectionFactory
				  .getConnectionHandler()
				  .getConnection()
				  .prepareStatement(
						"SELECT * FROM " + "sms,smsnotifications WHERE notificationtype = ? AND sms.nick = smsnotifications.name");
			ps.setInt(1, type);
			rs = ps.executeQuery();
		 } else {
			ps = DBConnectionFactory
				  .getConnectionHandler()
				  .getConnection()
				  .prepareStatement(
						"SELECT * FROM "
							  + "sms,smsnotifications WHERE name = ? AND notificationtype = ? AND sms.nick = smsnotifications.name");
			ps.setString(1, nick);
			ps.setInt(2, type);
			rs = ps.executeQuery();
		 }

		 while (rs.next()) {
			String message = "";
			if (rs.getInt("notificationtype") == 0) {
			   message = "Your army has come home in utopia";
			} else if (rs.getInt("notificationtype") == 1) {
			   message = "Wave time in utopia!";
			} else if (rs.getInt("notificationtype") == 2) {
			   message = "Your IRC alarm just went off";
			} else if (rs.getInt("notificationtype") == 3) {
			   message = "An order was just added";
			}
			UserHandler.sendSMS(rs.getString("name"), message, msg, Bot.getInstance().getNick());
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
   }

   /**
    * Returns the abbreviated name for the specified building
    * 
    * @param building
    * @return
    */
   public static String getBuildingAbbrev(final String building) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM " + "building WHERE name = ? OR abbreviation = ?");
		 ps.setString(1, building);
		 ps.setString(2, building);
		 rs = ps.executeQuery();

		 if (rs.next()) {
			out = rs.getString("abbreviation");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Maps a name or abbreviated name to the base name for a building
    * 
    * @param building
    * @return
    */
   public static String getBuildingsMapping(final String building) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM " + "building WHERE name = ? OR abbreviation = ?");
		 ps.setString(1, building);
		 ps.setString(2, building);
		 rs = ps.executeQuery();

		 if (rs.next()) {
			out = rs.getString("name");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns a regex that matches currently supported buildings Includes both full names and abbreviated names
    * 
    * @return
    */
   public static String getBuildingsRegex() {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM building");
		 rs = ps.executeQuery();

		 out = "(";
		 while (rs.next()) {
			out += rs.getString("name") + "|" + rs.getString("abbreviation") + "|";
		 }
		 out = out.substring(0, out.length() - 1) + ")";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns a regex that matches currently supported defensive specialist units
    * 
    * @return
    */
   public static String getDspecsRegex() {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM race");
		 rs = ps.executeQuery();

		 out = "(";
		 while (rs.next()) {
			out += rs.getString("dspecname") + "|";
		 }
		 out = out.substring(0, out.length() - 1) + ")";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns a regex that matches currently supported elite unites
    * 
    * @return
    */
   public static String getElitesRegex() {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM race");
		 rs = ps.executeQuery();

		 out = "(";
		 while (rs.next()) {
			out += rs.getString("elitename") + "|";
		 }
		 out = out.substring(0, out.length() - 1) + ")";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns a regex that matches the current links
    * 
    * @return
    */
   public static String getLinksRegex() {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM link");
		 rs = ps.executeQuery();

		 out = "(";
		 while (rs.next()) {
			out += rs.getString("name") + "|";
		 }
		 out = out.substring(0, out.length() - 1) + ")";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns a regex that matches currently supported offensive specialist units
    * 
    * @return
    */
   public static String getOspecsRegex() {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM race");
		 rs = ps.executeQuery();

		 out = "(";
		 while (rs.next()) {
			out += rs.getString("ospecname") + "|";
		 }
		 out = out.substring(0, out.length() - 1) + ")";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns the owner of the specified province. Empty string if none is found
    * 
    * @param prov
    * @return
    */
   public static String getOwner(final String prov) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM kdprovince WHERE province = ?");
		 ps.setString(1, prov);
		 rs = ps.executeQuery();
		 if (rs.next()) {
			out = rs.getString("mainnick");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns the base name of the specified personality
    * 
    * @param pers
    * @return
    */
   public static String getPersonalitiesMapping(final String pers) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM personality WHERE name = ? OR malealias = ? OR femalealias = ?");
		 ps.setString(1, pers);
		 ps.setString(2, pers);
		 ps.setString(3, pers);
		 rs = ps.executeQuery();

		 if (rs.next()) {
			out = rs.getString("name");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns a regex that matches currently supported personalities Includes the base name and both the male and female aliases
    * 
    * @return
    */
   public static String getPersonalitiesRegex() {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM personality");
		 rs = ps.executeQuery();

		 out = "(?i)(";
		 while (rs.next()) {
			out += rs.getString("name") + "|" + (rs.getString("malealias").equals("") ? "" : rs.getString("malealias") + "|")
				  + (rs.getString("femalealias").equals("") ? "" : rs.getString("femalealias") + "|");
		 }
		 out = out.substring(0, out.length() - 1) + ")";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns the personality of the specified user
    * 
    * @param nick
    * @return
    */
   public static String getPersonality(final String nick) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "Not registered";
	  try {
		 ps = DBConnectionFactory
			   .getConnectionHandler()
			   .getConnection()
			   .prepareStatement(
					 "SELECT personality FROM kdprovince,province WHERE "
						   + "province.name = kdprovince.province AND mainnick = ?");
		 ps.setString(1, nick);
		 rs = ps.executeQuery();
		 if (rs.next()) {
			out = rs.getString("personality");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns the name of the province the player is registered to. Empty string if no province is found
    * 
    * @param nick
    * @return
    */
   public static String getProv(final String nick) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT province FROM kdprovince WHERE mainnick = ?");
		 ps.setString(1, nick);
		 rs = ps.executeQuery();
		 if (rs.next()) {
			out = rs.getString("province");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns the race of the specified user
    * 
    * @param nick
    * @return
    */
   public static String getRace(final String nick) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "Not registered";
	  try {
		 ps = DBConnectionFactory
			   .getConnectionHandler()
			   .getConnection()
			   .prepareStatement(
					 "SELECT race FROM " + "kdprovince,province WHERE province.name = kdprovince.province AND mainnick = ?");
		 ps.setString(1, nick);
		 rs = ps.executeQuery();
		 if (rs.next()) {
			out = rs.getString("race");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns the base name for the specified race
    * 
    * @param race
    * @return
    */
   public static String getRacesMapping(final String race) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM race WHERE shortname = ?");
		 ps.setString(1, race);
		 rs = ps.executeQuery();

		 if (rs.next()) {
			out = rs.getString("name");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns a regex that matches currently supported races Whether to include abbreviated names is optional
    * 
    * @param includeShortNames
    * @return
    */
   public static String getRacesRegex(final boolean includeShortNames) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM race");
		 rs = ps.executeQuery();

		 out = "(?i)(";
		 while (rs.next()) {
			out += rs.getString("name") + "|" + (includeShortNames ? rs.getString("shortname") + "|" : "");
		 }
		 out = out.substring(0, out.length() - 1) + ")";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns a regex that matches currently supported science categories Includes base name, abbreviated name and Angel's name
    * for it
    * 
    * @return
    */
   public static String getScienceRegex() {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM science");
		 rs = ps.executeQuery();

		 out = "(";
		 while (rs.next()) {
			out += rs.getString("name") + "|" + rs.getString("abbreviation") + "|" + rs.getString("angel") + "|";
		 }
		 out = out.substring(0, out.length() - 1) + ")";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns the base name for the specified science category
    * 
    * @param science
    * @return
    */
   public static String getSciencesMapping(final String science) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM science WHERE name = ? OR angel = ?");
		 ps.setString(1, science);
		 ps.setString(2, science);
		 rs = ps.executeQuery();

		 if (rs.next()) {
			out = rs.getString("name");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns the abbreviated name for the specified race
    * 
    * @param race
    * @return
    */
   public static String getShortNameForRace(final String race) {
	  String out = "";
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM race WHERE name = ?");
		 ps.setString(1, race);
		 rs = ps.executeQuery();

		 if (rs.next()) {
			out = rs.getString("shortname");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns the base name for the specified title
    * 
    * @param title
    * @return
    */
   public static String getTitleMapping(final String title) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM " + "title WHERE name = ? OR " + "malealias = ? OR femalealias = ?");
		 ps.setString(1, title);
		 ps.setString(2, title);
		 ps.setString(3, title);
		 rs = ps.executeQuery();

		 if (rs.next()) {
			out = rs.getString("name");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns a regex that matches currently supported nobility titles Includes the base name and both the male and female aliases
    * 
    * @return
    */
   public static String getTitlesRegex() {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM title");
		 rs = ps.executeQuery();

		 out = "(";
		 while (rs.next()) {
			out += rs.getString("name") + "|" + (rs.getString("malealias").equals("") ? "" : rs.getString("malealias") + "|")
				  + (rs.getString("femalealias").equals("") ? "" : rs.getString("femalealias") + "|");
		 }
		 out = out.substring(0, out.length() - 1) + ")";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns the main nick of the user with the specified nick. Returns an empty string if no such user is found
    * 
    * @param nick
    * @param limitToMainNick
    * @return
    */
   public static String getUserFromDB(final String nick, final boolean limitToMainNick) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 if (limitToMainNick) {
			ps = DBConnectionFactory.getConnectionHandler().getConnection()
				  .prepareStatement("SELECT mainnick " + "FROM nickname WHERE mainnick = ? LIMIT 1");
		 } else {
			ps = DBConnectionFactory.getConnectionHandler().getConnection()
				  .prepareStatement("SELECT mainnick FROM nickname WHERE name = ?");
		 }
		 ps.setString(1, nick);
		 rs = ps.executeQuery();
		 if (rs.next()) {
			out = rs.getString("mainnick");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Uses fuzzy matching to find a user in the database. Returns the main nick if a match is found
    * 
    * @param nick
    * @return
    */
   public static String getUserFromDBFuzzyMatching(final String nick) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  String name = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM nickname WHERE name LIKE ?");
		 ps.setString(1, "%" + nick + "%");
		 rs = ps.executeQuery();

		 while (rs.next()) {
			if ("".equals(out)) {
			   out = rs.getString("mainnick");
			   name = rs.getString("name");
			} else {
			   if (StringTools.getLevenshteinDistance(nick, rs.getString("name")) < StringTools
					 .getLevenshteinDistance(nick, name)) {
				  out = rs.getString("mainnick");
				  name = rs.getString("name");
			   }
			}
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Returns the specified user's timezone
    * 
    * @param nick
    * @return
    */
   public static String getUserTimeZone(final String nick) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  double d = 0;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT timezone,dst FROM kdmember WHERE mainnick = ?");
		 ps.setString(1, nick);
		 rs = ps.executeQuery();
		 if (rs.next()) {
			d = rs.getDouble("timezone") + rs.getInt("dst");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  String out = "";
	  int i = (int) Math.floor(d);
	  if (Math.abs(i - d) < .0000001) {
		 out = i > 0 ? "+" + i : String.valueOf(i);
	  } else {
		 out = (i > 0 ? "+" + i : i) + ":30";
	  }
	  return out;
   }

   /**
    * Checks if the user is using the default password
    * 
    * @param user
    * @param password
    * @return
    */
   public static boolean hasDefaultBotAccount(final String user, final String password) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  boolean out = false;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM botaccount WHERE name = ?");
		 ps.setString(1, user);
		 rs = ps.executeQuery();
		 if (rs.next() && BCrypt.checkpw(password, rs.getString("password"))) {
			out = rs.getBoolean("defaultpw");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Checks if the specified kd exists in the database
    * 
    * @param loc
    * @return
    */
   public static boolean kdExists(final String loc) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  boolean out = false;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM kd WHERE loc = ?");
		 ps.setString(1, loc);
		 rs = ps.executeQuery();
		 out = rs.next();
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Lists currently supported buildings
    * 
    * @return
    */
   public static List<String> listBuildings() {
	  final ArrayList<String> out = new ArrayList<String>();
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM building");
		 rs = ps.executeQuery();

		 while (rs.next()) {
			out.add(rs.getString("name"));
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Lists currently supported personalities
    * 
    * @return
    */
   public static List<String> listPersonalities() {
	  final ArrayList<String> out = new ArrayList<String>();
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM personalities");
		 rs = ps.executeQuery();

		 while (rs.next()) {
			out.add(rs.getString("name"));
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Lists all current supported races
    * 
    * @return
    */
   public static List<String> listRaces() {
	  final ArrayList<String> out = new ArrayList<String>();
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM race");
		 rs = ps.executeQuery();

		 while (rs.next()) {
			out.add(rs.getString("name"));
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Lists the current support sciences
    * 
    * @return
    */
   public static List<String> listSciences() {
	  final ArrayList<String> out = new ArrayList<String>();
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM science");
		 rs = ps.executeQuery();

		 while (rs.next()) {
			out.add(rs.getString("name"));
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Lists currently supported titles
    * 
    * @return
    */
   public static List<String> listTitles() {
	  final ArrayList<String> out = new ArrayList<String>();
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM titles");
		 rs = ps.executeQuery();

		 while (rs.next()) {
			out.add(rs.getString("name"));
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Checks if the user supplied valid login info
    * 
    * @param user
    * @param password
    * @return
    */
   public static boolean loginBotAccount(final String user, final String password) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  boolean out = false;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT password FROM botaccount WHERE name = ?");
		 ps.setString(1, user);
		 rs = ps.executeQuery();
		 if (rs.next() && BCrypt.checkpw(password, rs.getString("password"))) {
			out = true;
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Checks if the specified province exists in the database
    * 
    * @param name
    * @return the province name. Empty string if no prov was found
    */
   public static String provinceExists(final String name) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM province WHERE name = ?");
		 ps.setString(1, name);
		 rs = ps.executeQuery();
		 if (rs.next()) {
			out = rs.getString("name");
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   /**
    * Checks if an offline user is an admin
    * 
    * @param nick
    * @return
    */
   public static boolean userIsAdminDB(final String nick) {
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  boolean out = false;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection()
			   .prepareStatement("SELECT * FROM admins WHERE mainnick = ?");
		 ps.setString(1, nick);
		 rs = ps.executeQuery();
		 out = rs.next();
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }

   private CommonMethods() {
	  // don't allow instantiation
   }
}
